1 import sys
2 import sqlite3
3 db = sqlite3.connect("rdbms_examination_result_project.db")
4 conn = db.cursor()
5
6 '''conn.execute("""CREATE TABLE STUDENT(
7 ROLL_NO INT PRIMARY KEY NOT NULL,
8 NAME TEXT NOT NULL,
9 FATHER_NAME TEXT NOT NULL,
10 MOBILE_NO INT NOT NULL,
11 AGE INT NOT NULL,
12 DEPARTMENT TEXT NOT NULL)""")
13
14 conn.execute("""CREATE TABLE RESULT(
15 ROLL_NO INT PRIMARY KEY NOT NULL,
16 EXAMINATION TEXT NOT NULL,
17 PYTHON INT NOT NULL,
18 RDBMS TEXT NOT NULL,
19 CN INT NOT NULL,
20 OS INT NOT NULL,
21 DS INT NOT NULL,
22 FOREIGN KEY (ROLL_NO) REFERENCES STUDENT(ROLL_NO))""")'''
23
24
25 def insert_data():
26 ch="y"
27 while(ch=="y" or ch=="Y"):
28 i=0
29 stu_roll_no=int(input("\n\nENTER THE ROLL NUMBER :"))
30
31 q0 = "SELECT * FROM STUDENT where ROLL_NO=?"
32 conn.execute(q0, (stu_roll_no,))
33 a=conn.fetchall()
34 for row in a:
35 if(stu_roll_no == row[0]):
36 print("\nTHIS ROLL NUMBER ALREADY EXISTS")
37 print("\n1.UPDATE THE EXISTING RECORD\n2.INSERT NEW RECORD\nPRESS ANY KEY TO GO BACK")
38 sel=input("\nENTER CHOICE : ")
39 if sel=='1':
40 update_data()
41 i=1
42 ch="n"
43 elif sel=='2':
44 ch="y"
45 i=1
46 else:
47 admin()
48 ch="n"
49 i=1
50
51 else:
52 break
53 if i!=1:
54 stu_name=input("ENTER STUDENT NAME :")
55 father_name=input("ENTER FATHER NAME :")
56 mobile=input("ENTER THE MOBILE NO :")
57 while(len(mobile)!=10):
58 mobile=input("\nPLEASE ENTER 10-DIGIT MOBILE NUMBER :")
59 stu_mobile_no=int(mobile)
60
61 stu_age=int(input("ENTER STUDENT AGE :"))
62 while(stu_age<=0 or stu_age>=30):
63 stu_age=int(input("\nENTER CORRECT STUDENT AGE"))
64
65 stu_dept=input("ENTER THE DEPARTMENT NAME :")
66 stu_dept.upper()
67 exam=input("ENTER EXAMINATION NAME :")
68 python=int(input("ENTER MARKS IN PYTHON :"))
69 rdbms=int(input("ENTER MARKS IN RDBMS :"))
70 cn=int(input("ENTER MARKS IN CN :"))
71 os=int(input("ENTER MARKS IN OS :"))
72 ds=int(input("ENTER MARKS IN DS :"))
73
74 conn.execute("insert into student (ROLL_NO,NAME,FATHER_NAME,MOBILE_NO,AGE,DEPARTMENT) values (?,?,?,?,?,?)",(stu_roll_no,stu_name,father_name,stu_mobile_no,stu_age,stu_dept))
75 conn.execute("insert into result (ROLL_NO,EXAMINATION,PYTHON,RDBMS,CN,OS,DS) values (?,?,?,?,?,?,?)",(stu_roll_no,exam,python,rdbms,cn,os,ds))
76
77 print("\n\nRECORD ENTERED SUCCESSFULLY")
78 db.commit()
79
80 ch=input("\n\nWANT TO INSERT ANOTHER RECORD ? (Y/N) : ")
81
82 def update_data():
83 ch="y"
84 u=0
85 rn=int(input("\nENTER STUDENT ROLL NUMBER TO UPDATE RECORD :"))
86 q0 = "SELECT * FROM STUDENT where ROLL_NO=?"
87 conn.execute(q0, (rn,))
88 a=conn.fetchall()
89 for row in a:
90 if(rn == row[0]):
91 while(ch=="y" or ch=="Y"):
92 u=1
93 print("\n1.UPDATE STUDENT NAME ")
94 print("2.UPDATE FATHER NAME ")
95 print("3.UPDATE MOBILE NUMBER ")
96 print("4.UPDATE STUDENT AGE ")
97 print("5.UPDATE DEPARTMENT ")
98 print("6.UPDATE MARKS IN PYTHON ")
99 print("7.UPDATE MARKS IN RDBMS ")
100 print("8.UPDATE MARKS IN CN ")
101 print("9.UPDATE MARKS IN OS ")
102 print("10.UPDATE MARKS IN DS ")
103 print("11.UPDATE EXAMINATION NAME ")
104 choice = int(input("\nENTER YOUR CHOICE : "))
105
106 if (choice == 1):
107 up_name = input("\nENTER STUDENT NAME :")
108 q5 = "UPDATE STUDENT SET NAME=? where ROLL_NO=?"
109 conn.execute(q5, (up_name, rn,))
110 print("\nNAME SUCCESSFULLY UPDATED")
111
112 elif(choice==2):
113 up_f_name = input("\nENTER FATHER NAME :")
114 q6 = "UPDATE STUDENT SET FATHER_NAME=? where ROLL_NO=?"
115 conn.execute(q6, (up_f_name, rn,))
116 print("\nFATHER NAME UPDATED SUCCESSFULLY")
117
118 elif(choice == 3):
119 mn = input("\nENTER MOBILE NUMBER :")
120 while(len(mn)!=10):
121 mn=input("\nPLEASE ENTER 10-DIGIT MOBILE NUMBER :")
122 up_mn=int(mobile)
123 q7 = "UPDATE STUDENT SET MOBILE_NO=? where ROLL_NO=?"
124 conn.execute(q7, (up_mn, rn,))
125 print("\nMOBILE NUMBER UPDATED SUCCESSFULLY")
126
127 elif(choice == 4):
128 up_age = int(input("\nENTER STUDENT AGE :"))
129 while(up_age<=0 or up_age>30):
130 up_age=int(input("\nENTER CORRECT STUDENT AGE"))
131 q15 = "UPDATE STUDENT SET AGE=? where ROLL_NO=?"
132 conn.execute(q15, (up_age, rn,))
133 print("\nAGE UPDATED SUCCESSFULLY")
134
135 elif(choice == 5):
136 up_dept = input("ENTER DEPARTMENT NAME :")
137 q8 = "UPDATE STUDENT SET DEPARTMENT=? where ROLL_NO=?"
138 conn.execute(q8, (up_dept, rn,))
139 print("\nDEPARTMENT UPDATED SUCCESSFULLY")
140
141 elif(choice == 6):
142 up_python = int(input("ENTER MARKS IN PYTHON :"))
143 q9 = "UPDATE RESULT SET PYTHON=? where ROLL_NO=?"
144 conn.execute(q9, (up_python, rn,))
145 print("\nMARKS IN PYTHON UPDATED SUCCESSFULLY")
146
147 elif(choice == 7):
148 up_rdbms = int(input("ENTER MARKS IN RDBMS :"))
149 q10 = "UPDATE RESULT SET RDBMS=? where ROLL_NO=?"
150 conn.execute(q10, (up_rdbms, rn,))
151 print("\nMARKS IN RDBMS UPDATED SUCCESSFULLY")
152
153 elif(choice == 8):
154 up_cn = int(input("ENTER MARKS IN CN :"))
155 q11 = "UPDATE RESULT SET CN=? where ROLL_NO=?"
156 conn.execute(q11, (up_cn, rn,))
157 print("\nMARKS IN CN UPDATED SUCCESSFULLY")
158
159 elif(choice == 9):
160 up_os = int(input("ENTER MARKS IN OS :"))
161 q12 = "UPDATE RESULT SET OS=? where ROLL_NO=?"
162 conn.execute(q12, (up_os, rn,))
163 print("\nMARKS IN OS UPDATED SUCCESSFULLY")
164
165 elif(choice == 10):
166 up_ds = int(input("ENTER MARKS IN DS :"))
167 q13 = "UPDATE RESULT SET DS=? where ROLL_NO=?"
168 conn.execute(q13, (up_ds, rn,))
169 print("\nMARKS IN DS UPDATED SUCCESSFULLY")
170
171 elif(choice == 11):
172 up_exam =input("ENTER EXAMINATION NAME :")
173 q14 = "UPDATE RESULT SET EXAMINATION=? where ROLL_NO=?"
174 conn.execute(q14, (up_exam, rn,))
175 print("\nEXAMINATION NAME UPDATED SUCCESSFULLY")
176 else:
177 print("\nINVALID CHOICE\nCHOOSE AGAIN\n")
178 ch="y"
179 u+=1
180 db.commit()
181 if u==1:
182 ch=input("\nWANT TO UPDATE MORE RECORDS ? (Y/N): ")
183 if u==0:
184 print("\nTHIS ROLL NUMBER DOES NOT EXISTS IN RECORD")
185 print("\n1. INSERT THIS INTO DATABASE\n2. BACK TO ADMIN MODE")
186 sel=int(input("\nENTER CHOICE : "))
187 if sel==1:
188 insert_data()
189 else:
190 admin()
191
192 def delete_data():
193 ch="y"
194 while(ch=="y" or ch=="Y"):
195 d=0
196 rn = int(input("\nENTER THE STUDENT ROLL NUMBER TO DELETE RECORD:"))
197 q0 = "SELECT * FROM STUDENT where ROLL_NO=?"
198 conn.execute(q0, (rn,))
199 a=conn.fetchall()
200 for row in a:
201 if(rn == row[0]):
202 q3 = "delete from STUDENT where ROLL_NO=?"
203 conn.execute(q3, (rn,))
204 q4 = "delete from RESULT where ROLL_NO=?"
205 conn.execute(q4, (rn,))
206 print("\nRECORD DELETED SUCCESSFULLY")
207 db.commit()
208 d=1
209
210 ch=input("\nWANT TO DELETE MORE RECORDS ? (Y/N) : ")
211 if d==0:
212 print("\nNO SUCH RECORD EXISTS IN THE DATABASE")
213 admin()
214
215 def user():
216 ch="y"
217 while ch=="y" or ch=="Y":
218 v=0
219 rn=int(input("\nENTER THE ROLL NO :"))
220 q1 = "SELECT * FROM STUDENT where ROLL_NO=?"
221 conn.execute(q1, (rn,))
222 a=conn.fetchall()
223 for row in a:
224 if(rn == row[0]):
225 print("\nROLL NUMBER :", row[0])
226 print("STUDENT NAME :", row[1])
227 print("FATHER NAME:", row[2])
228 print("PHONE NUMBER :", row[3])
229 print("STUDENT AGE :", row[4])
230 print("DEPARTMENT :", row[5])
231 v=1
232 else:
233 break
234 q2 = "SELECT * FROM RESULT where ROLL_NO=?"
235 conn.execute(q2, (rn,))
236 b=conn.fetchall()
237 for row1 in b:
238 if(rn==row1[0]):
239 print("\nEXAMINATION NAME :", row1[1])
240 print("MARKS IN PYTHON :", row1[2])
241 print("MARKS IN RDBMS :", row1[3])
242 print("MARKS IN CN :", row1[4])
243 print("MARKS IN OS :", row1[5])
244 print("MARKS IN DS :", row1[6])
245 v=2
246 else:
247 break
248 if v!=2:
249 print("\nTHERE IS NO SUCH RECORD")
250 sel=int(input("\n1.VIEW ANOTHER\n2.RETURN\nENTER CHOICE : "))
251 if sel==1:
252 ch="y"
253 else:
254 return
255 else:
256 ch=input("\nWANT TO VIEW ANOTHER RECORD ? (Y/N): ")
257
258 def login():
259 username=input("\nENTER THE USERNAME :")
260 if(username=="ARMAN" or username=="arman"):
261 password=input("ENTER THE PASSWORD :")
262 if(password=="password"):
263 print("\nLOGIN SUCCESSFUL")
264 admin()
265 else:
266 print("\nINVALID CREDENTIALS")
267 login()
268 else:
269 print("\nINVALID CREDENTIALS")
270 login()
271
272 def view():
273 q1 = "SELECT * FROM STUDENT"
274 conn.execute(q1)
275 a=conn.fetchall()
276 print("\n........STUDENT TABLE........")
277 for row in a:
278 print("\n\nROLL NUMBER :", row[0])
279 print("STUDENT NAME :", row[1])
280 print("FATHER NAME:", row[2])
281 print("PHONE NUMBER :", row[3])
282 print("STUDENT AGE :", row[4])
283 print("DEPARTMENT :", row[5])
284 q2 = "SELECT * FROM RESULT"
285 conn.execute(q2)
286 b=conn.fetchall()
287 print("\n........RESULT TABLE........")
288 for row1 in b:
289 print("\n\nROLL NUMBER :", row1[0])
290 print("EXAMINATION NAME :", row1[1])
291 print("MARKS IN PYTHON :", row1[2])
292 print("MARKS IN RDBMS :", row1[3])
293 print("MARKS IN CN :", row1[4])
294 print("MARKS IN OS :", row1[5])
295 print("MARKS IN DS :", row1[6])
296
297 def admin():
298 ch="y"
299 while(ch=="y" or ch=="Y"):
300
301 print("\n1. INSERT NEW RECORD IN DATABASE")
302 print("2. UPDATE RECORD IN DATABASE")
303 print("3. DELETE RECORD FROM DATABASE")
304 print("4. SHOW DATABASE")
305 print("5. EXIT")
306 choice = int(input("\nENTER YOUR CHOICE :"))
307 if (choice==1):
308 insert_data()
309 elif (choice== 2):
310 update_data()
311 elif (choice==3):
312 delete_data()
313 elif(choice==4):
314 view()
315 elif(choice==5):
316 sys.exit()
317 else:
318 print("\nPLEASE ENTER THE VALID CHOICE")
319 admin()
320 ch=input("\nWANT TO CONTINUE TO ADMIN MODE ?(Y/N) : ")
321
322 #main program
323 ch="y"
324 while(ch=="y" or ch=="Y"):
325 print("\n\n..........EXAMINATION RESULT MANAGEMENT SYSYTEM............")
326 print("\n\n\n1. ADMINISTRATION MODE")
327 print("2. USER MODE")
328 print("3. EXIT")
329 choice=int(input("\nENTER YOUR CHOICE :"))
330 if(choice == 1):
331 login()
332 ch="n"
333 elif(choice == 2):
334 user()
335 ch="n"
336 elif(choice == 3):
337 sys.exit()
338 else:
339 print("\nINVALID CHOICE")
340 sys.exit()
